use SMS
go
---------------------------------------------
------------Methods to get messages----------
---------------------------------------------

---1. sp_getAllMessages

create proc sp_getMessage
as
begin
	select sms_message.msg_id,
		   sms_message.msg_content,
		   sms_message.msg_from,
		   sms_message.msg_to,
		   sms_message.msg_date
	from sms_message
	order by sms_message.msg_id
end
go

---2. sp_getMessagesByID

create proc sp_getMessageByID
	@msg_id int
as
begin
	select sms_message.msg_id,
		   sms_message.msg_content,
		   sms_message.msg_from,
		   sms_message.msg_to,
		   sms_message.msg_date
	from sms_message
	where msg_id=@msg_id
end
go

---------------------------------------------
------------ Methods to update --------------
---------------------------------------------

create procedure sp_updateMessage 
	@msg_id int,
	@new_content nvarchar(160)
as
begin
	update sms_message 
	set sms_message.msg_content = @new_content 
	where sms_message.msg_id=@msg_id
end
go

---------------------------------------------
------------ Methods to delete message ------
---------------------------------------------

create procedure sp_deleteMessage
	@msg_id int
as 
begin
	delete from sms_message 
	where sms_message.msg_id = @msg_id
end
go
---------------------------------------------
----------- Methods to create a new message--
---------------------------------------------

create procedure sp_createANewMessage
	@msg_content nvarchar(max),
	@msg_from nchar(20),
	@msg_to nchar(20)
as
begin
	insert into sms_message(msg_content,msg_from,msg_to)
		   values(@msg_content,@msg_from,@msg_to)
end
go

select * from sms_message